using System;
using System.Data;
using System.Text;
using System.Collections;

using PIM.Utilities.Logging;
using PIM.Utilities.DataAccess;

namespace PIM.Utilities
{
	/// <summary>
	/// Summary description for QSecureDBHelper.
	/// </summary>
	public class QSecureDBHelper
	{
		public const  string  SCHEDULE = "Scheduler";
		public const string LOGICALSUBSYSTEM = "LogicalSubSystem";
        private PIMManager dbMgr = null;
		public string qry = string.Empty;
		public string SystemName = "";
		public string ObjectName = "";
		public const int LvAppID = 3;
		public int ParentNavID = -1;
		public int NavID = -1;
		public int ContentID = -1;

		#region Queries
		/*private const string SEL_FIELD_PROPS_FOR_QUERY =	"SELECT ISEXTENDED, OBJECTTYPE , DATASTORENAME, NAME, " +
			"QUERY FROM OBJECTFIELDPROPERTIES OP JOIN CONDITIONS " + 
			"C ON C.FIELD = OP.FIELDID WHERE C.OBJECTID = {0} AND " +
			"OBJECTNAME = '{1}' AND OP.OBJECTTYPE IN (2,4) " +
			"ORDER BY QUERY, NAME";*/
        
        // for GTAA
        private const string SEL_FIELD_PROPS_FOR_QUERY = "SELECT OP.SYSTEMID, OP.ISEXTENDED, OP.OBJECTID , OP.DATASTORENAME, OP.NAME AS FieldName, C.QUERY, "+
            " (SELECT TABLENAME FROM APPLICATIONOBJECTS WHERE OBJECTID = OP.OBJECTID)  AS TABLENAME, " +
            " (SELECT NAME FROM APPLICATIONOBJECTS WHERE OBJECTID = OP.OBJECTID)  AS APPOBJECTNAME " +            
            "FROM OBJECTPROPERTIES OP JOIN CONDITIONS C " +
            "ON C.FIELD = OP.propertyID WHERE C.OBJECTID = {0} AND OBJECTNAME = '{1}' " +
            "AND OP.OBJECTID IN (SELECT op.objectid FROM OBJECTPROPERTIES WHERE op.PROPERTYID in " +
            " (SELECT field FROM CONDITIONS WHERE OBJECTID = {0})) ORDER BY QUERY, NAME ";
        // 

        private const string SEL_SCHEDULE_PROPS = "SELECT CHANNELQ, CHANNELMACHINE , SYSTEMPROPVALUE SCHEDULEPOLLTIME " +
			"FROM CHANNELS, (SELECT SYSTEMPROPVALUE FROM OBJ_SYSTEMPROPERTIES WHERE " + 
			"SYSTEMPROPID = (SELECT ID FROM OBJ_SYSTEMPROPERTYLIST WHERE NAME = 'SchedulePollTime' " +
			"AND SYSTEMTYPE = 1 AND OBJECTTYPE = 1) AND SYSTEMID = 1) TEMP " +
			"WHERE CHANNELNAME = (SELECT SYSTEMPROPVALUE FROM OBJ_SYSTEMPROPERTIES WHERE SYSTEMPROPID = " +
			"(SELECT ID FROM OBJ_SYSTEMPROPERTYLIST WHERE NAME = 'ScheduleChannelName' AND SYSTEMTYPE = 1 " +
			"AND OBJECTTYPE = 1) AND SYSTEMID = 1)";


        private const string SEL_OBJECT_DET = "SELECT {2}, '{0}' AS TableName FROM {0} WHERE {1} ";

		private const string SEL_PERSONS_TOP =	"SELECT DISTINCT E.PersonID FROM Obj_PersonExtendedValues E " + 
			"JOIN Obj_Person P on E.PersonID = P.PhysobjectID AND E.SystemID = {0} {1}";

		private const string SEL_PERSONS_RECUR =	"SELECT PERSONID FROM (SELECT * FROM Map_Person_Clearance MPC " +
			"JOIN Obj_Clearance OC on OC.ClearanceID = MPC.Clearid " +
			"AND OC.SystemID ={0}) MC WHERE MC.TYPE = '{1}' " +
			"AND MC.{2} {3} {4} ###";

		private const string SEL_PERSONS_RECUR_WO = "SELECT PERSONID FROM (SELECT * FROM Map_Person_Clearance MPC " +
			"JOIN Obj_Clearance OC on OC.ClearanceID = MPC.Clearid " +
			"AND OC.SystemID ={0}) MC WHERE MC.TYPE = '{1}' " +
			"{3} {4} ###";

		private const string SEL_PERSON_E = "{0} E.PERSONID IN ({1})";

		private const string SEL_PERSON_MC = "AND MC.PERSONID IN ({0})";
		
		private const string GENERATE_ALLLOGICALSUBSYSTEMDATA = "INSERT INTO MAP_LOGICALSUBSYSTEMPERSONS (LOGICALSUBSYSTEMID, PERSONID) "+
			"(SELECT DISTINCT {0}, PERSONID FROM ({1})A)";

		/*private const string GENERATE_PERSONLOGICALSUBSYSTEMDATA = "INSERT INTO MAP_LOGICALSUBSYSTEMPERSONS (LOGICALSUBSYSTEMID, PERSONID) "+
			"(SELECT DISTINCT {0}, PERSONID FROM ({1} AND PERSONID = {2}) A";*/
		private const string GENERATE_PERSONLOGICALSUBSYSTEMDATA = "INSERT INTO MAP_LOGICALSUBSYSTEMPERSONS (LOGICALSUBSYSTEMID, PERSONID) "+
			"(SELECT DISTINCT {0}, PERSONID FROM ({1} AND PERSONID = {2})A )";

		private const string DELETE_ALLLOGICALSUBSYSTEMDATA = "DELETE FROM MAP_LOGICALSUBSYSTEMPERSONS WHERE LOGICALSUBSYSTEMID = {0}";

		private const string DELETE_PERSONLOGICALSUBSYSTEMDATA = "DELETE FROM MAP_LOGICALSUBSYSTEMPERSONS WHERE LOGICALSUBSYSTEMID = {0} AND PERSONID={1}";

		private const string SEL_LOGICALSUBSYSTEMS = "SELECT LOGICALSUBSYSTEMID,SYSTEMID FROM LOGICALSUBSYSTEMS";
		private const string SEL_NavigationID = "SELECT NavigationID FROM UI_MapSubApps2NavigationTree WHERE SubAppID=(SELECT SubAppID FROM subapplications WHERE Name='Manager' AND AppID={0} AND IsVisible=1)  ";
		private const string SEL_IDContentPageID = "SELECT ID, ContentPageID FROM UI_NavigationTree WHERE ParentID = {0} AND DisplayIndex={1}";
		//private const string DEL_UI_NavigationTree = "DELETE FROM UI_NavigationTree WHERE ParentID={0} AND ID = {0}";
		private const string DEL_UI_NTree_ParentID = "DELETE FROM UI_NavigationTree WHERE ParentID={0}";
		private const string DEL_UI_NTree_ID = "DELETE FROM UI_NavigationTree WHERE ID={0}";
		private const string SEL_ReportID = "SELECT ReportID FROM UI_ContentPages WHERE ContentPageID ={0}";
		private const string DEL_UI_ContentActions = "DELETE FROM UI_ContentActions WHERE ContentPageID = {0}";
		private const string DEL_UI_ContentPages = "DELETE FROM UI_ContentPages WHERE ContentPageID = {0}";
		private const string DEL_Reports_info = "DELETE FROM ReportSorts WHERE ReportID={0}" +";"+ "DELETE FROM ReportColumnOrder WHERE ReportID={0}" +";"+ "DELETE FROM ReportColumns WHERE ReportID={0}" +";"+ "DELETE FROM Reports WHERE ID={0}";
		private const string SEL_Obj_ManagedSystems = "SELECT  SystemAlias, SystemName FROM Obj_ManagedSystems WHERE SystemID={0}";
		private const string SEL_SubAppID = "SELECT SubAppID FROM SubApplications WHERE AppID={0}AND IsVisible =1 AND Name=N'Manager'";
		private const string INSERT_Reports = "INSERT INTO dbo.Reports ( ID, Name, Type, AppID, UniqueColumnName, Parameters, Query, UserID, Conditions, DataObjectName)VALUES ({0}, N'{1}', 1, 3, 'col1', 'col1', 'SELECT 1', 1, 0, N'{2}')";
		private const string SEL_SysColumns = "SELECT SC.Name,dbo.QSISNULL(OFP.DisplayName,SC.Name) AS DisplayName FROM SysColumns SC LEFT JOIN ObjectFieldProperties OFP ON SC.Name=OFP.Name AND OFP.ObjectType=2 AND ofp.SystemID={0} WHERE id=(SELECT id FROM sysobjects WHERE xtype IN ('V') AND Name=N'{1}') AND xtype NOT IN (34,35,99)";
		private const string INS_ReportColumns = "INSERT INTO ReportColumns (ReportID, ColumnID,ColumnName,DisplayName,ColumnWidth) VALUES ({0},{1},N'{2}',N'{3}',150)";
		private const string UPDATE_Reports_Query = "UPDATE Reports SET Query = N'{0}' WHERE ID = {1}";
		private const string UPDATE_Reports_Parameters = "UPDATE Reports SET Parameters = (SELECT ColumnName FROM ReportColumns WHERE ReportID = {0} AND DisplayName='PersonID') WHERE ID={0}";
		private const string UPDATE_Reports_UniqueColumnName = "UPDATE Reports SET UniqueColumnName = (SELECT ColumnName+',LogicalSubSystemID' FROM ReportColumns WHERE ReportID = {0} AND DisplayName='PersonID') WHERE ID={0}";
		private const string INS_ReportColumnOrder = "INSERT INTO ReportColumnOrder (ReportID,ColumnID,OrderIndex,IsGroupBy,UserID) VALUES ({0},{1},{2},0,1)";
		private const string INS_ReportSorts = "INSERT INTO ReportSorts (ReportID,SortColumnID,OrderBy,UserID) VALUES({0},1,0,1)";
		private const string INS_UI_ContentPages = "INSERT INTO UI_ContentPages (ContentPageID,Name, DisplayHeader, ReportID, URL) VALUES ({0},N'{1}', 1, {2}, NULL)";
		private const string SEL_Reportcolumns = "SELECT ReportID,ColumnID, ColumnID AS OrderIndex FROM Reportcolumns WHERE ReportID={0} AND ColumnID <6";
		
		protected const string jsview = "window.open(''popup.aspx?HelpText='' + HelpText[0] + ''&ActionType=3&FormID=1&SystemID='' + SYSTEMID +''&paramid=''+PARAMID,''Person'',''left=200,top=80,Height=620,width=700,toolbar=0,resizable=1,location=0,status=0,menubar=0,scrollbars=1'');";
		protected const string jsCreate = "try {SYSTEMID=SYSTEMID; window.open(''popup_safemngr.aspx?HelpText='' + HelpText[1] + ''&ActionType=1&FormID=5&SystemID='' + SYSTEMID ,''Person'',''left=200,top=80,Height=620,width=700,toolbar=0,resizable=1,location=0,status=0,menubar=0,scrollbars=1'');}catch(e){alert(''Please select a System.'');}";
		protected const string jsedit = "try {SYSTEMID=SYSTEMID; window.open(''popup_safemngr.aspx?HelpText='' + HelpText[2] + ''&ActionType=2&FormID=5&SystemID='' + SYSTEMID+''&ParamID=''+PARAMID ,''Person'',''left=200,top=80,Height=620,width=700,toolbar=0,resizable=1,location=0,status=0,menubar=0,scrollbars=1'');}catch(e){alert(''Please select a System.'');}";
		protected const string jsSearch = "try {SYSTEMID=SYSTEMID; window.open(''search_popup_safemngr.aspx?ActionType=1&FormID=5&SystemID='' + SYSTEMID ,''SearchPerson'',''left=200,top=150,Height=600,width=700,toolbar=0,resizable=1,location=0,status=0,menubar=0,scrollbars=1'');}catch(e){alert(''Please select a System.'');}";
		protected const string jsExport = "window.open(''Export.aspx?ReportType='' + REPORTTYPE + ''&CurrentPage='' + Form1.hdCurrentIndex.value,'''',''height=222,width=310,left=300,top=250,noresize,scrollbars=no'');return false;";
		
		private const string INS_UI_View = "INSERT INTO UI_ContentActions (ActionID, Name, ActionType, ActiveImageFile, DeactiveImageFile, DisplayIndex, ActionScriptURL, ContentPageID, FormID)	VALUES ({0}, '', 12, 'viewdetails.gif', 'viewdetails.gif', 1, N'{1}', {2}, NULL) ";
		private const string INS_UI_Create = "INSERT INTO UI_ContentActions (ActionID, Name, ActionType, ActiveImageFile, DeactiveImageFile, DisplayIndex, ActionScriptURL, ContentPageID, FormID)	VALUES ({0}, '', 1, 'addperson.jpg', 'viewdetails.jpg', 2, N'{1}', {2}, NULL)";
		private const string INS_UI_Edit = "INSERT INTO UI_ContentActions (ActionID, Name, ActionType, ActiveImageFile, DeactiveImageFile, DisplayIndex, ActionScriptURL, ContentPageID, FormID)	VALUES ({0}, '', 11, 'editperson.jpg', 'viewdetails.jpg', 3, N'{1}', {2}, NULL)";
		private const string INS_UI_Refresh = "INSERT INTO UI_ContentActions (ActionID, Name, ActionType, ActiveImageFile, DeactiveImageFile, DisplayIndex, ActionScriptURL, ContentPageID, FormID)	VALUES ({0}, 'Refresh', 10, 'RefreshBtn.GIF', 'RefreshBtn.GIF', 25, 'window.location.reload();', {1}, NULL)";
		private const string INS_UI_Search = "INSERT INTO UI_ContentActions (ActionID, Name, ActionType, ActiveImageFile, DeactiveImageFile, DisplayIndex, ActionScriptURL, ContentPageID, FormID)	VALUES ({0}, 'Search Person', 10, 'search.gif', 'search.gif', 15, N'{1}', {2}, NULL)";
		private const string INS_UI_Export = "INSERT INTO UI_ContentActions (ActionID, Name, ActionType, ActiveImageFile, DeactiveImageFile, DisplayIndex, ActionScriptURL, ContentPageID, FormID) VALUES ({0}, 'Export', 10, 'export.gif', 'export.gif', 20,N'{1}', {2}, NULL)";
		
		private const string INS_UI_NavigationTree = "INSERT INTO UI_NavigationTree (ID,Name, ParentID, DisplayIndex, Query, ImageFile, ContentPageID, FilterValues) VALUES( {0},'', {1}, {2}, N'{3}', NULL, {4}, NULL)";
		private const string SEL_SystemName = "SELECT SystemAlias FROM Obj_ManagedSystems WHERE SystemID={0}";
		private const string SEL_ObjectName = "SELECT [Name] FROM ObjectTypes WHERE ID={0}";
		private const string CREATE_MasterDataViewDef = "CREATE VIEW {0} AS ( SELECT Name AS Type, StorageValue, DisplayValue,ParentStorageValue, ParentName AS ParentType, IsProcessed, OperationType, MessageID FROM InboundMasterDataStore Where SystemID={1} AND ObjectType ={2})";
		private const string CREATE_MasterDataTriggerDef = "CREATE TRIGGER {0} ON {1} INSTEAD OF INSERT AS INSERT INTO InboundMasterDataStore (SystemID, ObjectType, Name, StorageValue, DisplayValue, ParentStorageValue, ParentName, IsProcessed, CreationDate, ModifiedDate, MessageID ) SELECT {2}, {3}, Type, StorageValue, DisplayValue, ParentStorageValue, ParentType, 0, GETDATE(), GETDATE(), MessageID FROM INSERTED ";
		private const string SEL_InboundViewDef = "CREATE VIEW {0} AS ( SELECT p.physobjectid as PersonID %PERSONFIELDLIST% %EXTENDEDFIELDSLIST% from obj_person p, obj_personextendedvalues e where p.physobjectid = e.PersonID and SystemID = {1} ) ";	
		private const string SEL_PersonFields = "SELECT ' ,' + [Name] as fieldname FROM objectfieldproperties where systemid = {0} and objecttype = {1} and IsExtended = 0 ";
		private const string SEL_PersonExtFields = "SELECT NAME, DATASTORENAME FROM objectfieldproperties where  systemid = {0} and objecttype ={1} and IsExtended = 1";
		private const string SEL_DBObjectname = "SELECT dbobjectname FROM reportviews where dbobjectname = N'{0}'";
		private const string INS_ReportViews = "Insert INTO reportviews (ReportViewID,reportobject, dbobjectname) values ({0}, N'{1}', N'{1}')";
		private const string SEL_StagingTable ="SELECT SystemAlias, StagingTable, WriteStagingTable FROM Obj_ManagedSystems WHERE SystemID={0}";
		private const string CREATE_InboundViewDef = "CREATE VIEW {0} AS ( SELECT OperationType, IsProcessed, MessageID, SafeXML,  ";
		private const string CREATE_OutboundViewDef = "CREATE VIEW {0} AS ( SELECT ID AS Row_ID, ISProcessed, OperationType, MessageID, CreationDate, SafeXML,   ";
		private const string CREATE_SafeOutboundViewDef = "CREATE VIEW {0} AS ( SELECT OperationType, MessageID, SafeXML,  ";
        private const string CREATE_InboundTriggerDef = "CREATE TRIGGER {0} ON {1} INSTEAD OF INSERT AS BEGIN INSERT INTO {2} (SystemID, ObjectType, IsProcessed, OperationType, MessageID, SafeXML, CreationDate, ModifiedDate%STAGINGCOLUMNS% ) SELECT {3}, {4}, ISNULL(IsProcessed,0), ISNULL(OperationType,1), MessageID, SafeXML, GETDATE(), GETDATE()%MSSCOLUMNS% FROM INSERTED END";
		private const string CREATE_InboundUpdateTriggerDef = "CREATE TRIGGER {0} ON {1} INSTEAD OF UPDATE AS BEGIN IF UPDATE(IsProcessed) BEGIN UPDATE InboundDataStore set IsProcessed = INSERTED.IsProcessed %UPDATESTAGINGCOLUMNS% FROM INSERTED WHERE %UPDATEKEYCOLUMNS% AND SYSTEMID ={2} AND OBJECTTYPE ={3} END ELSE BEGIN UPDATE InboundDataStore set IsProcessed = 0 %UPDATESTAGINGCOLUMNS% FROM INSERTED WHERE %UPDATEKEYCOLUMNS% AND SYSTEMID = {2} AND OBJECTTYPE ={3} END END";
		private const string CREATE_SafeOutboundTriggerDef = "CREATE TRIGGER {0} ON {1} INSTEAD OF INSERT AS BEGIN INSERT INTO {2} (SystemID, ObjectType, IsProcessed, OperationType,MessageID, SafeXML, CreationDate, ModifiedDate%STAGINGCOLUMNS% ) SELECT {3},{4}, 0, ISNULL(OperationType,1), MessageID, SafeXML, GETDATE(), GETDATE()%SAFECOLUMNS% FROM INSERTED END";
		private const string SEL_ObjFields = "SELECT Name, MSSName, DataStoreName, IsIdentifier FROM ObjectFieldProperties WHERE SystemID={0} and ObjectType={1}";
		private const string SEL_CreateFrom = "{0} FROM {1} where SystemID = {2} AND ObjectType = {3} )";

		#endregion 

        public QSecureDBHelper(PIMManager dbManager)
		{
			try
			{
				dbMgr = dbManager;				
			}		
			catch(Exception )
			{
				throw;
			}
		}
		public static string ConvertToDBDate(string date, DBProvider DBPROVIDER)
		{
			switch (DBPROVIDER)
			{
				case DBProvider.Oracle:
					return string.Format("to_date('{0}','MM/DD/YYYY hh:mi:ss AM')", date);
				default:
					return string.Format("'{0}'",date);
			}
		}

		public void UpdateLogicalSubSystem(int logicalsubsystemid, int systemid, int personid)
		{
			string query;
			try
			{
				//delete existing data
				if (personid ==0)
					dbMgr.Execute(string.Format(DELETE_ALLLOGICALSUBSYSTEMDATA,logicalsubsystemid));
				else
					dbMgr.Execute(string.Format(DELETE_PERSONLOGICALSUBSYSTEMDATA,logicalsubsystemid,personid));

				query = GetQueryForPersons(logicalsubsystemid, systemid, LOGICALSUBSYSTEM);
				
				if(query != string.Empty)
				{
					if (personid == 0)
						query = string.Format(GENERATE_ALLLOGICALSUBSYSTEMDATA,logicalsubsystemid,query);
					else
						query = string.Format(GENERATE_PERSONLOGICALSUBSYSTEMDATA,logicalsubsystemid,query,personid);
					dbMgr.Execute(query);
				}
				
			}
			catch(Exception )
			{
				throw;
			}

		}

		public void UpdatePersonLogicalSubSystem(int personid)
		{
			try
			{
				DataTable dt = dbMgr.GetData(SEL_LOGICALSUBSYSTEMS);
				foreach (DataRow dr in dt.Rows)
				{
					UpdateLogicalSubSystem(Convert.ToInt32(dr["LogicalSubSystemID"]),Convert.ToInt32(dr["SystemID"]),personid);
				}

			}
			catch(Exception)
			{
				throw;
			}
		}

		public DataTable GetPersonsForSchedule(int scheduleid, int systemid)
		{	
			DataTable dtPersons = null;
			string query;
			try
			{
				query = GetQueryForPersons(scheduleid, systemid, SCHEDULE);
				if(query != string.Empty)
				{
					dtPersons = dbMgr.GetData(query);
				}
				
			}
			catch(Exception)
			{
				throw;
			}

			return(dtPersons);
		}


        public int GetScheduleMainObjectID(PIMManager dbMgr, int systemid, int scheduleid)
        {
            string SELECTMAINOBJECTID = "SELECT MAINOBJECTID FROM SCHEDULES WHERE SYSTEMID = {0} AND ID = {1}";

            object result = dbMgr.GetValue(string.Format(SELECTMAINOBJECTID, systemid, scheduleid));

            return (result is System.DBNull ? -1 : Convert.ToInt32(result));
        }

		public DataRow GetScheduleProperties()
		{
			DataRow result;
			try
			{

				result = dbMgr.GetDataRow(SEL_SCHEDULE_PROPS);
				
			}
			catch(Exception )
			{
				throw;

			}
			return(result);
		}


		
         public string GetQueryForPersons(int id, int systemid, string type)
		{
			DataTable dtFieldProperties;
			string query;
			string prevClearanceType = string.Empty;
			StringBuilder sbQuery = new StringBuilder();
			StringBuilder sbClearanceQuery = new StringBuilder();
			StringBuilder sbMainQuery = new StringBuilder();

			try
			{
				if(systemid != -1)
				{
					
					//Get the expressions related to scheduleid
					query = string.Format(SEL_FIELD_PROPS_FOR_QUERY, id, type);

					dtFieldProperties = dbMgr.GetData(query);

					if(dtFieldProperties != null)
					{
						#region Create hash table for other than mssclear_ID
						DataView dtView = new DataView(dtFieldProperties);
						dtView.RowFilter = "NAME NOT IN ('MSSClear_ID') AND OBJECTTYPE = 4";
						prevClearanceType = string.Empty;
						Hashtable htFieldProperties = null;
						if(dtView.Count > 0)
						{	
							htFieldProperties = new Hashtable();
							for(int count = 0; count < dtView.Count; count++)
							{
								string fieldValue = Convert.ToString(dtView[count]["QUERY"]);
								string[] values = fieldValue.Split('|');
								string strTemp;
								if(htFieldProperties.ContainsKey(values[0]))
								{								
									htFieldProperties[values[0]] = string.Format("{0} AND {1} {2}",
										htFieldProperties[values[0]],
										Convert.ToString(dtView[count]["NAME"]),
										values[1]);									
								}
								else
								{
									strTemp = string.Format(" AND {0} {1}",
										Convert.ToString(dtView[count]["NAME"]),
										values[1]);
									htFieldProperties.Add(values[0], strTemp);
								}
							}
						}
						#endregion

						prevClearanceType = string.Empty;
						for(int count = 0; count < dtFieldProperties.Rows.Count; count++)
						{
							bool isExtended = QSecureHelper.ConvertToBool(dtFieldProperties.Rows[count]["ISEXTENDED"]);
							int objectType = Convert.ToInt32(dtFieldProperties.Rows[count]["OBJECTTYPE"]);
								
							#region For ObjectType = 2
							if(objectType == 2)
							{
								if(isExtended)
								{
									sbQuery.AppendFormat(" AND E.{0} {1}",
										dtFieldProperties.Rows[count]["DATASTORENAME"], 
										dtFieldProperties.Rows[count]["QUERY"]);
								}
								else
								{
									sbQuery.AppendFormat(" AND P.{0} {1}",
										dtFieldProperties.Rows[count]["NAME"], 
										dtFieldProperties.Rows[count]["QUERY"]);
								}
							}
								#endregion
								#region For ObjectType = 4
							else if(objectType == 4)
							{
								string fieldValue = Convert.ToString(dtFieldProperties.Rows[count]["QUERY"]);
								string[] values = fieldValue.Split('|');

								string strOthers = string.Empty;
								if(htFieldProperties != null)
								{
									if(htFieldProperties.ContainsKey(values[0]))
									{
										strOthers = Convert.ToString(htFieldProperties[values[0]]);
									}
								}							

								if(Convert.ToString(dtFieldProperties.Rows[count]["NAME"]) == "MSSClear_ID")
								{
									if(prevClearanceType == string.Empty)
									{
										prevClearanceType = values[0];
										//Starting initialize stringbuilders
										sbClearanceQuery.AppendFormat(SEL_PERSON_E, "", string.Format(SEL_PERSONS_RECUR,
											systemid,
											values[0],
											dtFieldProperties.Rows[count]["NAME"],
											values[1],
											strOthers));
									}
									else
									{
										if(prevClearanceType == values[0])
										{
											//same add a recurr string
											sbClearanceQuery.Replace("###",
												string.Format(SEL_PERSON_MC, 
												string.Format(SEL_PERSONS_RECUR,
												systemid,
												values[0],
												dtFieldProperties.Rows[count]["NAME"],
												values[1]
												,strOthers)
												));

										}
										else
										{
											prevClearanceType = values[0];
											//different so complete the old string
											sbClearanceQuery.Replace("###", "");
											//append the new string
											sbClearanceQuery.AppendFormat(SEL_PERSON_E, " AND ",
												string.Format(SEL_PERSONS_RECUR,
												systemid,
												values[0],
												dtFieldProperties.Rows[count]["NAME"],
												values[1],
												strOthers)
												);
										}
									}
								}
							}
							#endregion
						}

						#region Check for particular Clearance Type Hash table is there or not 
						if (htFieldProperties != null)
						{
							foreach(string ClearanceType in htFieldProperties.Keys)
							{
								dtView = new DataView(dtFieldProperties);
								dtView.RowFilter = string.Format("QUERY LIKE '{0}|%' AND NAME IN ('MSSClear_ID') AND OBJECTTYPE = 4", ClearanceType);
								if(dtView.Count == 0)
								{
									string strOthers = Convert.ToString(htFieldProperties[ClearanceType]);
									//different so complete the old string
									sbClearanceQuery.Replace("###", "");
									//append the new string
									sbClearanceQuery.AppendFormat(SEL_PERSON_E, " AND ",
										string.Format(SEL_PERSONS_RECUR_WO,
										systemid,
										ClearanceType,
										"",
										"",
										strOthers)
										);
								}
							}
						}
						#endregion

						if(sbClearanceQuery.Length > 0)
						{
							sbClearanceQuery.Replace("###", "");
						}

						string tempString = " AND ";
						if(sbQuery.Length > 0)
						{
							sbQuery = sbQuery.Replace(tempString, "", 0, tempString.Length);
						}


						if(sbClearanceQuery.Length > 0)
						{
							sbClearanceQuery = sbClearanceQuery.Replace(tempString, "", 0, tempString.Length);
						}

						if(sbQuery.Length > 0)
						{
							sbMainQuery.AppendFormat(SEL_PERSONS_TOP,
								systemid,
								string.Format(" WHERE {0} ", sbQuery.ToString())
								);
						}

						if(sbClearanceQuery.Length > 0)
						{
							if(sbMainQuery.Length == 0)
							{
								sbMainQuery.AppendFormat(SEL_PERSONS_TOP,
									systemid,
									string.Format(" WHERE {0} ", sbClearanceQuery.ToString())
									);
							}
							else
							{
								sbMainQuery.AppendFormat(" AND {0} ", sbClearanceQuery.ToString());
							}
						}
					}
				}
			}
			catch(Exception )
			{
				throw;
			}
			return(sbMainQuery.ToString());
		}

        public string GetScheduleCondition(int id, int systemid, string type, out string objName)
        {
            DataTable dtFieldProperties;
            string query;            
            StringBuilder sbConditions = new StringBuilder();

            try
            {
                objName = string.Empty;

                    //Get the expressions related to scheduleid
                    query = string.Format(SEL_FIELD_PROPS_FOR_QUERY, id, type);
                    dtFieldProperties = dbMgr.GetData(query);

                    if (dtFieldProperties != null)
                    {
                        string lsrtTableName = string.Empty;
                        string lstrIdName = string.Empty;
                        
                        foreach (DataRow dr in dtFieldProperties.Rows)
                        {
                            lsrtTableName = Convert.ToString(dr["APPOBJECTNAME"]);
                            //lstrIdName = Convert.ToString(dr["IdentifierName"]);
                            sbConditions.AppendFormat("{0} {1} AND ", dr["FieldName"], dr["Query"]);
                            
                        }

                        if (sbConditions.Length > 0)
                        {
                            sbConditions.Length -= 5;
                        }

                        objName = lsrtTableName;                        
                    }                
            }
            catch (Exception)
            {
                throw;
            }

            return (sbConditions.ToString());
        }
	    public void CreateSystemReport(int SystemID)
		{
			string ViewName = "";
			string LvSystemName = "";
			string Fields = "";
			string ColName = "";
			string DisplayName = "";
			string ReportQuery = "";
			string TreeQuery = "";
			int LvSubAppID = -1;
			int ReportID = -1;
			int ColumnID = 1;
			int ActionID = -1;
			int NavID1 = -1;
			DataTable DT1 = new DataTable();
			
			try
			{
				DeleteSystemReport(SystemID);
				dbMgr.BeginTrans();
				qry = string.Format(SEL_Obj_ManagedSystems,SystemID);
				DT1 = dbMgr.GetData(qry);
				if(DT1 != null)
				{
					foreach( DataRow DR1 in DT1.Rows )
					{
						ViewName = DR1["SystemAlias"].ToString() + "Person";
						LvSystemName = DR1["SystemName"].ToString();
					}
				}
				DT1.Clear();
				qry = string.Format(SEL_SubAppID,LvAppID);
				LvSubAppID = Convert.ToInt32(dbMgr.GetValue(qry));
			
				//Create Report
				ReportID = dbMgr.GetNextObjectID("Reports");
				qry = string.Format(INSERT_Reports,ReportID,"Personnel Manager : "+LvSystemName,ViewName);
				dbMgr.Execute(qry);
				
				qry = string.Format(SEL_SysColumns,SystemID,ViewName);
				DT1 = dbMgr.GetData(qry);
				if(DT1 != null)
				{
					foreach ( DataRow dr in DT1.Rows )
					{
						ColName = dr["Name"].ToString();
						DisplayName = dr["DisplayName"].ToString();
						Fields += "OP." + ColName + " AS Col" + ColumnID.ToString() + "," ;
						qry = string.Format(INS_ReportColumns,ReportID,ColumnID,"col"+ColumnID.ToString(),DisplayName);
						dbMgr.Execute(qry);
						ColumnID = ColumnID+1;
					}
				}
				if(ColumnID > 1)
				{
					Fields = Fields.Substring(0, Fields.Length-1);
				}
				DT1.Clear();
				ReportQuery = "SELECT" +SystemID.ToString()+" AS SYSTEMID, MLS.LogicalSubSystemID, OP.PersonID," + Fields + " FROM " + ViewName + " OP LEFT JOIN MAP_LOGICALSUBSYSTEMPERSONS MLS ON OP.PERSONID=MLS.PERSONID LEFT JOIN LOGICALSUBSYSTEMS LS ON LS.SYSTEMID=" + SystemID.ToString() + "WHERE (MLS.LOGICALSUBSYSTEMID IS NULL AND" + SystemID.ToString() + "IN (SELECT GC.ObjectValue FROM GROUPACCESSCONTROL GC WHERE GC.ACCESSOBJID = 3 AND GC.Permission IN (1,2,3) AND GC.GROUPID IN (SELECT MUG.GroupID FROM Map_Users_Groups MUG INNER JOIN Users U ON U.ID=MUG.UserID WHERE U.ID=" +"@SAFEUserID"+ "))) OR (MLS.LogicalSubsystemID IN (SELECT GC.ObjectValue FROM GROUPACCESSCONTROL GC WHERE GC.ACCESSOBJID = 5 AND GC.Permission >0 AND GC.GROUPID IN (SELECT MUG.GroupID FROM Map_Users_Groups MUG INNER JOIN Users U ON U.ID=MUG.UserID WHERE U.ID=" +"@SAFEUserID"+ ")))";
				qry = string.Format(UPDATE_Reports_Query,ReportQuery,ReportID);
				dbMgr.Execute(qry);
				qry = string.Format(UPDATE_Reports_Parameters,ReportID);
				dbMgr.Execute(qry);
				qry = string.Format(UPDATE_Reports_UniqueColumnName,ReportID);
				dbMgr.Execute(qry);
				qry = string.Format(SEL_Reportcolumns,ReportID);
				DT1 = dbMgr.GetData(qry);
				foreach( DataRow dr in DT1.Rows )
				{
					qry = string.Format(INS_ReportColumnOrder,dr["ReportID"],dr["ColumnID"],dr["OrderIndex"]);
					dbMgr.Execute(qry);
				}
				DT1.Clear();
				qry = string.Format(INS_ReportSorts,ReportID);
				dbMgr.Execute(qry);

				// CREATE UI Content Page
				ContentID = dbMgr.GetNextObjectID("UI_ContentPages");
				qry = string.Format(INS_UI_ContentPages,ContentID,LvSystemName+ " Persons",ReportID);
				dbMgr.Execute(qry);
				
				// CREATE Content Actions
				ActionID = dbMgr.GetNextObjectID("UI_ContentActions");
				qry = string.Format(INS_UI_View,ActionID,jsview,ContentID);
				dbMgr.Execute(qry);
				ActionID += 1;
				qry = string.Format(INS_UI_Create,ActionID,jsCreate,ContentID);
				dbMgr.Execute(qry);
				ActionID += 1;
				qry = string.Format(INS_UI_Edit,ActionID,jsedit,ContentID);
				dbMgr.Execute(qry);
				ActionID += 1;
				qry = string.Format(INS_UI_Refresh,ActionID,ContentID);
				dbMgr.Execute(qry);
				ActionID += 1;
				qry = string.Format(INS_UI_Search,ActionID,jsSearch,ContentID);
				dbMgr.Execute(qry);
				ActionID += 1;
				qry = string.Format(INS_UI_Export,ActionID,jsExport,ContentID);
				dbMgr.Execute(qry);
				
				// Create UI_NavigationTree with System & SubSystems
				// ADD node TO Systems Root.
				qry = string.Format(SEL_NavigationID,LvAppID);
				ParentNavID = Convert.ToInt32(dbMgr.GetValue(qry));
				
				NavID = dbMgr.GetNextObjectID("UI_NavigationTree");
				TreeQuery = "SELECT SYSTEMNAME,SYSTEMID FROM OBJ_MANAGEDSYSTEMS WHERE SYSTEMID =" + SystemID.ToString();
				qry = string.Format(INS_UI_NavigationTree,NavID,ParentNavID,SystemID,TreeQuery,ContentID);
				dbMgr.Execute(qry);

               	NavID1 = dbMgr.GetNextObjectID("UI_NavigationTree");
				TreeQuery = "SELECT NAME,LOGICALSUBSYSTEMID from LOGICALSUBSYSTEMS WHERE SYSTEMID=" +SystemID.ToString(); 
				qry = string.Format(INS_UI_NavigationTree,NavID1,NavID,1,TreeQuery,ContentID);
				dbMgr.Execute(qry);
				dbMgr.CommitTrans();
				
			}
			catch(Exception ex)
			{
                GlobalLog.Logger.LogError(ex.ToString());
				dbMgr.RollBackTrans();
				dbMgr.Close();
			}
		}
		public void DeleteSystemReport (int SystemID)
		{
			int ReportID =-1;
			try
			{
				qry = string.Format(SEL_NavigationID,LvAppID);
				ParentNavID = Convert.ToInt32(dbMgr.GetValue(qry));
				qry = string.Format(SEL_IDContentPageID,ParentNavID,SystemID);
				DataTable DT2 = dbMgr.GetData(qry);
				if(DT2 != null)
				{
					foreach (DataRow DR2 in DT2.Rows)
					{
						NavID = Convert.ToInt32(DR2["ID"]);
						ContentID = Convert.ToInt32(DR2["ContentPageID"]);
					}
				}
				DT2.Clear();
				if(NavID != -1)
				{
					qry = string.Format(SEL_ReportID,ContentID);
					ReportID = Convert.ToInt32(dbMgr.GetValue(qry));
					
					dbMgr.BeginTrans();
					qry = string.Format(DEL_UI_NTree_ParentID,NavID);
					dbMgr.Execute(qry);
					qry = string.Format(DEL_UI_NTree_ID,NavID);
					dbMgr.Execute(qry);

					// Delete Content Actions & Pages
					qry = string.Format(DEL_UI_ContentActions,ContentID);
					dbMgr.Execute(qry);
					qry = string.Format(DEL_UI_ContentPages,ContentID);
					dbMgr.Execute(qry);
					
					// Delete Report related info
					qry = string.Format(DEL_Reports_info,ReportID);
					dbMgr.Execute(qry);
					dbMgr.CommitTrans();
				}
			}
			catch(Exception ex)
			{
                GlobalLog.Logger.LogError(ex.ToString());
				dbMgr.RollBackTrans();
				dbMgr.Close();
			}
			
		}
        public void CreateMasterDataViews(int SystemID, int ObjectID)
		{
			string MasterDataViewName ="";
			string MasterDataTriggerName = "";
			string MasterDataViewDef = "";
			string MasterDataTriggerDef = "";
			try
			{
				qry = string.Format(SEL_SystemName, SystemID);
				SystemName = dbMgr.GetValue(qry).ToString();
				qry = string.Format(SEL_ObjectName,ObjectID);
				ObjectName = dbMgr.GetValue(qry).ToString();
			
				MasterDataViewName = "IExt"+SystemName+"_"+ObjectName+"MD";
				MasterDataTriggerName = "IOT_"+MasterDataViewName;

				DataTable DT_MasterDataViews = dbMgr.GetTables();
				DataRow[] DR_MasterDataViews =  DT_MasterDataViews.Select( "Name='" + MasterDataViewName + "'");
				if( DR_MasterDataViews.Length > 0 )
				{
					qry = "DROP View" + MasterDataViewName;
					dbMgr.BeginTrans();
					dbMgr.Execute(qry);
					dbMgr.CommitTrans();
				}
				MasterDataViewDef = string.Format(CREATE_MasterDataViewDef,MasterDataViewName,SystemID,ObjectID);
				MasterDataTriggerDef = string.Format(CREATE_MasterDataTriggerDef,MasterDataTriggerName ,MasterDataViewName ,SystemID.ToString(),ObjectID.ToString() );
				dbMgr.BeginTrans();
				dbMgr.Execute(MasterDataViewDef);
				dbMgr.Execute(MasterDataTriggerDef);
				dbMgr.CommitTrans();
			}
			catch(Exception ex)
			{
                GlobalLog.Logger.LogError(ex.ToString());
				dbMgr.RollBackTrans();
				dbMgr.Close();
			}
		}
	    public void CreateInternalViewsForSystems(int SystemID, int ObjectID)
        {
			string PersonFieldList = "";
			string ExtendedPersonFieldList = "";
			string FieldName = "";
			string InboundViewDef = "";
			string ViewName = "";
			
			try
			{
				dbMgr.BeginTrans();
				qry = string.Format(SEL_SystemName, SystemID);
				SystemName = dbMgr.GetValue(qry).ToString();
				qry = string.Format(SEL_ObjectName,ObjectID);
				ObjectName = dbMgr.GetValue(qry).ToString();

				ViewName = SystemName+ObjectName;

				DataTable DT_InternalViews = dbMgr.GetTables();
				DataRow[] DR_InternalViews = DT_InternalViews.Select("Name = '"+ViewName+"'");
				if( DR_InternalViews.Length > 0)
				{
					qry = "DROP View" + ViewName;
					dbMgr.Execute(qry);
				}
				DT_InternalViews.Clear();
			
				//			SELECT 'AFTER DELETE'
	
				InboundViewDef = string.Format(SEL_InboundViewDef,ViewName,SystemID);
				qry = string.Format(SEL_PersonFields,SystemID,ObjectID);
				DT_InternalViews = dbMgr.GetData(qry);
				if(DT_InternalViews != null)
				{
					foreach( DataRow dr in DT_InternalViews.Rows )
					{
						FieldName = dr["fieldname"].ToString();
						PersonFieldList += FieldName; 
					}
				}
				DT_InternalViews.Clear();
				qry = string.Format(SEL_PersonExtFields,SystemID,ObjectID);
				DT_InternalViews = dbMgr.GetData(qry);
				if(DT_InternalViews != null)
				{
					foreach( DataRow dr in DT_InternalViews.Rows )
					{
						string Name = dr["NAME"].ToString();
						string DATASTORENAME = dr["DATASTORENAME"].ToString();
						FieldName = ","+DATASTORENAME + " AS " + Name;
						ExtendedPersonFieldList += FieldName; 
					}
				}
				DT_InternalViews.Clear();
				
				InboundViewDef = InboundViewDef.Replace("%PERSONFIELDLIST%",PersonFieldList);
				InboundViewDef = InboundViewDef.Replace("%EXTENDEDFIELDSLIST%",ExtendedPersonFieldList);
				dbMgr.Execute(InboundViewDef);
			
				// add the view to the report objects list
				
				qry = string.Format(SEL_DBObjectname,ViewName);
				object obj_Name = dbMgr.GetValue(qry);
				
				if(obj_Name == null )
				{
					int ReportViewID = dbMgr.GetNextObjectID("reportviews");
					qry = string.Format(INS_ReportViews,ReportViewID,ViewName);
					dbMgr.Execute(qry);
				}
				dbMgr.CommitTrans();
			}
			catch(Exception ex)
			{
                GlobalLog.Logger.LogError(ex.ToString());
				dbMgr.RollBackTrans();
				dbMgr.Close();
			}
		}
		public void CreateViewsForExternalSystems(int SystemID,int ObjectID)
		{
			string ReadStagingTable = "";
			string WriteStagingTable = "";
			string InboundViewName = "";
			string OutboundViewName= "";
			string SafeOutboundViewName = "";
			string InboundTriggerName = "";
			string SafeOutboundTriggerName = "";
			string InboundViewDef = "";
			string OutboundViewDef = "";
			string SafeOutboundViewDef = "";
			string InboundUpdateTriggerName = "";
			string InboundUpdateTriggerDef = "";
			string UpdateStagingColumns = "";
			string UpdateKeyColumns = "";
			string InboundTriggerDef = "";
			string SafeOutboundTriggerDef = "";
			string MSSColumns = "";
			string StagingColumns = "";
			string SafeColumns = "";
			string ExtViewCol = "";
			string IntViewCol = "";
			string MSSCol = "";
			string StagingCol = "";
			string SafeCol = "";
			bool IsIdentifer ;			
			
			try
			{
				//Create the views required for Master Data upload
				//2 is person and 3 is clearance.
				
				if( (ObjectID == 2)|| (ObjectID == 3))
				{
					CreateMasterDataViews(SystemID,ObjectID);
				}
			
				//Ignore object type 3 - create views only for person and personclearance (NOT for clearance)
				qry = string.Format(SEL_StagingTable,SystemID);
				dbMgr.BeginTrans();
				DataTable DT_ExternalViews = dbMgr.GetData(qry);
				
				foreach( DataRow dr in DT_ExternalViews.Rows )
				{  
					SystemName = dr["SystemAlias"].ToString();
					ReadStagingTable = dr["StagingTable"].ToString();
					WriteStagingTable = dr["WriteStagingTable"].ToString();
				}
				DT_ExternalViews.Clear();
				qry = string.Format(SEL_ObjectName,ObjectID);
				ObjectName = dbMgr.GetValue(qry).ToString();
				InboundViewName = "IExt"+SystemName+"_"+ObjectName;
				OutboundViewName = "OExt"+SystemName+"_"+ObjectName;
				SafeOutboundViewName = "OInt"+SystemName+"_"+ObjectName;

				InboundTriggerName = "IOI"+InboundViewName;
				SafeOutboundTriggerName = "IOI"+SafeOutboundViewName;
				InboundUpdateTriggerName = "IOU"+InboundViewName;
				DT_ExternalViews = dbMgr.GetTables();
				DataRow[] DR_ExternalSystems = DT_ExternalViews.Select("Name = '"+InboundViewName+"'");
				if (DR_ExternalSystems.Length >0)
				{
					qry = "DROP View" + InboundViewName;
					dbMgr.Execute(qry);
				}
				DR_ExternalSystems = DT_ExternalViews.Select("Name = '"+OutboundViewName+"'");
				if (DR_ExternalSystems.Length >0)
				{
					qry = "DROP View" + OutboundViewName;
					dbMgr.Execute(qry);
				}
				DR_ExternalSystems = DT_ExternalViews.Select("Name = '"+SafeOutboundViewName+"'");
				if (DR_ExternalSystems.Length >0)
				{
					qry = "DROP View" + SafeOutboundViewName;
					dbMgr.Execute(qry);
				}

				DT_ExternalViews.Clear();			
				InboundViewDef = string.Format(CREATE_InboundViewDef,InboundViewName);
				OutboundViewDef = string.Format(CREATE_OutboundViewDef,OutboundViewName );
				SafeOutboundViewDef = string.Format(CREATE_SafeOutboundViewDef,SafeOutboundViewName);
				InboundTriggerDef = string.Format(CREATE_InboundTriggerDef,InboundTriggerName,InboundViewName,ReadStagingTable,SystemID,ObjectID);
				if(ObjectID != 4)
				{
					InboundUpdateTriggerDef = string.Format(CREATE_InboundUpdateTriggerDef,InboundUpdateTriggerName,InboundViewName,SystemID,ObjectID);
				}

				SafeOutboundTriggerDef = string.Format(CREATE_SafeOutboundTriggerDef,SafeOutboundTriggerName,SafeOutboundViewName,WriteStagingTable,SystemID,ObjectID);
		
				qry = string.Format(SEL_ObjFields ,SystemID,ObjectID);
				DT_ExternalViews = dbMgr.GetData(qry);
				
				foreach ( DataRow dr in DT_ExternalViews.Rows )
				{
					SafeCol = dr["Name"].ToString(); 
					MSSCol = dr["MSSName"].ToString();
					StagingCol = dr["DataStoreName"].ToString();
					IsIdentifer = Convert.ToBoolean(dr["IsIdentifier"]);
					ExtViewCol = "["+StagingCol +"] AS ["+ MSSCol +"],";
					IntViewCol = "["+StagingCol +"] AS ["+ SafeCol +"],";

					if(StagingCol != "" && StagingCol != null)
					{
						InboundViewDef = InboundViewDef+" "+ExtViewCol;
						OutboundViewDef = OutboundViewDef+" "+ExtViewCol;
						SafeOutboundViewDef = SafeOutboundViewDef+" "+IntViewCol;
			
						MSSColumns = MSSColumns+", "+MSSCol;
						StagingColumns = StagingColumns+", "+StagingCol;
						SafeColumns = SafeColumns+", "+SafeCol;
						if ( ObjectID != 4 )
						{
							UpdateStagingColumns = UpdateStagingColumns + ","+ StagingCol + "= INSERTED."+MSSCol;
							if( IsIdentifer )
							{
								UpdateKeyColumns = " INSERTED."+MSSCol +"="+"INBOUNDDATASTORE."+StagingCol;
								//SELECT 'key ' + @UpdateKeyColumns
							}
						}
					}
				
				}
				DT_ExternalViews.Clear();
				dbMgr.CommitTrans();
				if (InboundViewDef.Substring(0,InboundViewDef.Length).EndsWith(","))
				{
					//select @InboundViewDef
					InboundViewDef = InboundViewDef.Substring(0,InboundViewDef.Length-1);
					InboundViewDef= string.Format(SEL_CreateFrom,InboundViewDef,ReadStagingTable,SystemID,ObjectID);  
					
					//select @OutboundViewDef
					OutboundViewDef = OutboundViewDef.Substring(0,OutboundViewDef.Length-1);
					OutboundViewDef = string.Format(SEL_CreateFrom,OutboundViewDef,WriteStagingTable,SystemID,ObjectID );
					
					//select @SafeOutboundViewDef
					SafeOutboundViewDef = SafeOutboundViewDef.Substring(0,SafeOutboundViewDef.Length-1);				
					SafeOutboundViewDef = string.Format(SEL_CreateFrom,SafeOutboundViewDef,WriteStagingTable,SystemID,ObjectID);
					
					//select @InboundTriggerDef
					InboundTriggerDef = InboundTriggerDef.Replace("%STAGINGCOLUMNS%",StagingColumns);
					InboundTriggerDef = InboundTriggerDef.Replace("%MSSCOLUMNS%",MSSColumns);
					
					//select @SafeOutboundTriggerDef
					SafeOutboundTriggerDef = SafeOutboundTriggerDef.Replace("%STAGINGCOLUMNS%",StagingColumns);
					SafeOutboundTriggerDef = SafeOutboundTriggerDef.Replace("%SAFECOLUMNS%",SafeColumns);
					
					dbMgr.BeginTrans();
					dbMgr.Execute(InboundViewDef);
					dbMgr.Execute(OutboundViewDef);
					dbMgr.Execute(SafeOutboundViewDef);
					dbMgr.Execute(InboundTriggerDef);
					dbMgr.Execute(SafeOutboundTriggerDef);
					if(ObjectID != 4)
					{
					//select @IObjectType
						InboundUpdateTriggerDef = InboundUpdateTriggerDef.Replace("%UPDATESTAGINGCOLUMNS%", UpdateStagingColumns);
						InboundUpdateTriggerDef = InboundUpdateTriggerDef.Replace("%UPDATEKEYCOLUMNS%", UpdateKeyColumns);
						dbMgr.Execute(InboundUpdateTriggerDef);
					}
					dbMgr.CommitTrans();
				}
			
				if(ObjectID == 2)
				{
					CreateInternalViewsForSystems(SystemID, ObjectID);
				}
			}
			catch(Exception ex)
			{
                GlobalLog.Logger.LogError(ex.ToString());
				dbMgr.RollBackTrans();
				dbMgr.Close();
			}
		}
	}
}


